package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bean.MessageBean;
import com.bean.RecordBean;
import com.db.JdbcUtil;
import com.exception.DBException;
import com.exception.StateQueryException;

public class StateQueryDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private ArrayList<RecordBean> list = new ArrayList<RecordBean>();

	private ArrayList<RecordBean>  executeSQL(String sql) throws DBException, StateQueryException
	{
		con = JdbcUtil.getConnection();
		RecordBean rb = null;
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				rb = new RecordBean();
				rb.setBarcode(rs.getString(1));
				rb.setInsName(rs.getString(2));
				rb.setIfIn(rs.getString(3));
				rb.setUserID(rs.getString(4));
				rb.setFirstname(rs.getString(5));
				rb.setLastname(rs.getString(6));
				rb.setEmail(rs.getString(7));
				rb.setBorrowDate(rs.getDate(8));
				rb.setSBackDate(rs.getDate(9));
				list.add(rb);
			}

		}
		catch (SQLException e)
		{
			e.printStackTrace();
			throw new StateQueryException("Fail to get the state");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public ArrayList<RecordBean>  qState_Barcode(MessageBean message) throws DBException, StateQueryException
	{
		String sql = "select d.barcode,d.name,d.ifin,a.userID,a.firstname,a.lastname,a.email,c.borrowDate,c.sbackDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where c.backDate is null and d.barcode ='" + message.getValue() +"'";
		return this.executeSQL(sql);
	}

	public ArrayList<RecordBean>  qState_InsName(MessageBean message) throws DBException, StateQueryException
	{
		String sql = "select d.barcode,d.name,d.ifin,a.userID,a.firstname,a.lastname,a.email,c.borrowDate,c.sbackDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where c.backDate is null and d.name like '%" + message.getValue()+"%'";
		return this.executeSQL(sql);
	}

	public ArrayList<RecordBean>  qState_UserID(MessageBean message) throws DBException, StateQueryException
	{
		String sql = "select d.barcode,d.name,d.ifin,a.userID,a.firstname,a.lastname,a.email,c.borrowDate,c.sbackDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where c.backDate is null and c.userID = '" + message.getValue() +"'";
		return this.executeSQL(sql);
	}

}
